package com.softer.chengxin.scheduled.dao;

import com.softer.chengxin.score.entity.ScoreComposite;
import com.softer.chengxin.score.entity.ScoreDetail;
import com.softer.chengxin.score.entity.ScoreRecord;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/10/17
 */
@Mapper
@Repository
public interface ScheduledTaskDao {
    /**
     * 定时查询失效时间，过期将状态改为失效
     */
    @Update("UPDATE score_detail SET state =8 WHERE effective_end_time<NOW()")
    void updateEffective();

    /**
     * 定时查询状态为“公示”的公示结束时间，过期将状态改为生效
     */
    @Update("UPDATE score_detail SET state=7 WHERE state=2 AND publicity_end_time<NOW()")
    void updatePublicityTime();

    /**
     * 增加一条定时任务时间
     *
     * @param time 设定的时间
     * @param name 添加人
     * @return 结果
     */
    @Insert("INSERT INTO scheduled_time (gmt_create,gmt_modified,time,adder) VALUES(NOW(),NOW(),#{time},#{name})")
    int addTime(@Param("time") String time, @Param("name") String name);

    /**
     * 获取最近一次设定的时间
     *
     * @return 时间
     */
    @Select("SELECT time FROM scheduled_time ORDER BY gmt_create DESC limit 1")
    String getTime();

    /**
     * 查询各个企业的有效优良总得分和负面总得分
     *
     * @return
     */
    @Select("SELECT b.corpId as companyId,MAX(CASE b.type WHEN 1 THEN b.score ELSE 0 END) as excellentScore,MAX(CASE b.type WHEN 2 THEN b.score ELSE 0 END) as negativeScore" +
            " FROM (SELECT a.corpId,a.type,SUM(a.score) as score FROM (SELECT d.corp_id as corpId, d.score_type_id as scoreTypeId,t.type,IFNULL((CASE WHEN t.type=1 AND SUM(d.score)>= t.max_score THEN t.max_score ELSE SUM(d.score) END ),0) AS score " +
            " FROM score_detail d LEFT JOIN score_type t ON d.score_type_id = t.id WHERE d.publicity_begin_time <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND d.effective_end_time >= DATE_FORMAT(NOW(),'%Y-%m-%d')" +
            " GROUP BY d.corp_id, d.score_type_id)a GROUP BY a.corpId,a.type)b GROUP BY b.corpId")
    List<ScoreComposite> getScoreCompositeList();

    /**
     * 查询企业有效时间内的各种得分
     *
     * @param corpId 企业Id
     * @return
     */
    @Select("SELECT d.score_type_id as scoreTypeId,t.type,IFNULL((CASE WHEN t.type=1 AND SUM(d.score)>= t.max_score THEN t.max_score ELSE SUM(d.score) END ),0) AS score " +
            " FROM score_detail d LEFT JOIN score_type t ON d.score_type_id = t.id " +
            " WHERE d.publicity_begin_time <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND d.effective_end_time >= DATE_FORMAT(NOW(),'%Y-%m-%d') AND d.corp_id=#{id}" +
            " GROUP BY d.score_type_id")
    List<ScoreDetail> getScoreList(@Param("id") Integer corpId);
}
